package cn.stylefeng.guns.sys.modular.system.service;

import cn.stylefeng.guns.base.pojo.page.LayuiPageFactory;
import cn.stylefeng.guns.sys.modular.system.entity.BtycDdpx;
import cn.stylefeng.guns.sys.modular.system.mapper.BtyDdpxMapper;
import cn.stylefeng.roses.core.util.ToolUtil;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.util.List;
import java.util.Map;

@Service
public class BtyDdpxService extends ServiceImpl<BtyDdpxMapper, BtycDdpx> {

    @Autowired
    private BtyDdpxMapper btyDdpxMapper;

    /**
     * 获取员工培训列表
     */
    public Page<Map<String, Object>> list(String condition) {
        Page page = LayuiPageFactory.defaultPage();
        return this.baseMapper.list(page);
    }

    /**
     * 获取员工培训列表（不分页）
     */
    public List<BtycDdpx> listAll() {
        return this.baseMapper.listAll();
    }

    /**
     * 导入excel
     */
    @Transactional(readOnly = false, rollbackFor = Exception.class)
    public int batchImport(String fileName, MultipartFile file) throws Exception {
        this.baseMapper.delete();
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            throw new Exception("上传文件格式不正确");
        }
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        InputStream is = file.getInputStream();
        Workbook wb = null;
        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        //excel中多个sheet表，获取所有sheet表的数量
        int sheets = wb.getNumberOfSheets();
        for (int i = 0; i < sheets; i++) {
            Sheet sheet = wb.getSheetAt(i);
            int lastRowNum = sheet.getLastRowNum() - 1;
            for (int r = 1; r <= lastRowNum; r++) {
                Row row = sheet.getRow(r);
                if (row == null) {
                    continue;
                }
                //获取单元格
                Cell cell = row.getCell(0);
                if (ToolUtil.isNotEmpty(cell)) {
                    cell.setCellType(CellType.STRING);
                }
                String date = row.getCell(15) == null ? "" : row.getCell(15).getStringCellValue().replaceAll("年", "-").replaceAll("月", "-").replaceAll("日", "");
                DataFormatter dataFormatter = new DataFormatter();
                String name = row.getCell(0) == null ? "" : dataFormatter.formatCellValue(row.getCell(0));
                String id = row.getCell(1) == null ? "" : dataFormatter.formatCellValue(row.getCell(1));
                String bm = row.getCell(4) == null ? "" : row.getCell(4).getStringCellValue();
                String ks = row.getCell(5) == null ? "" : row.getCell(5).getStringCellValue();
                String tgqk = row.getCell(6) == null ? "" : row.getCell(6).getStringCellValue();
                BtycDdpx btyDdpx = new BtycDdpx();
                Map<String, String> map = btyDdpxMapper.yd(id);
                if (id != null && !"".equals(id)) {
                    btyDdpx.setId(id);
                    btyDdpx.setKs(ks);
                    btyDdpx.setName(name);
                    btyDdpx.setDate(date);
                    btyDdpx.setBm(bm);
                    btyDdpx.setTgqk(tgqk);
                    if (map != null) {
                        String yyjl = map.get("yyjl") == null ? "" : map.get("yyjl");
                        String dd = map.get("dd") == null ? "" : map.get("dd");
                        btyDdpx.setDd(dd);
                        btyDdpx.setYyjl(yyjl);
                    } else {
                        btyDdpx.setDd("");
                        btyDdpx.setYyjl("");
                    }
                    btyDdpxMapper.insert(btyDdpx);
                }
            }
        }
        return 1;
    }
}
